<?php

class KliController extends Zend_Controller_Action
{
    public function init()
    {
        $this->db   = Zend_Registry::get('db');
    }

    public function indexAction()
    {
    }

    public function nekudotAction()
    {

    	$slct = $this->db->select();

    	$when = $this->_getParam('when');

    	switch ( $when ) {
    		case 'today':
    			$slct->where('V.In >= CURDATE()');
    			break;

    		case 'yesterday':
    			$slct->where('V.In BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE()');
    			break;

    		case 'realtime':
    		default:
    			$slct->where('V.In < NOW() AND V.Out > NOW()');
    			break;
		}

    	$slct->from(
    				array( 'V' => 'Vsts' ),
    				array()
    			);

    	$slct->joinLeft(
    				array( 'L' => 'Lctns' ),
    				'V.Lctns_Id = L.Id',
    				array( 'Lat','Lng' )
    			);


		$stmt 		= $this->db->query( $slct );

//		die( $slct->__toString() );

		$this->view->data = $stmt->fetchAll();
    }

    public function dashboardAction()
    {

    	// Total People

	    	$TotalPeople = $this->db->select();

			$TotalPeople->from(
								array( 'V' => 'Vsts' ),
								array( 'TotalPeople' => new Zend_Db_Expr('MAX(V.Frnds)') )
			);


		// People by activity


	    	$People = $this->db->select();

			$People->from(
								array( 'V' => 'Vsts' ),
								array(
										'TotalPeople' 	=> new Zend_Db_Expr(' MAX(V.Frnds) + COUNT( DISTINCT V.Usrs_Id )'),
										'Usrs'			=> new Zend_Db_Expr(' COUNT( DISTINCT V.Usrs_Id ) ')
								)
			);

			$People->joinLeft(
								array( 'A' => 'Actvs' ),
								'V.Actvs_Id = A.Id',
								array(
										'Id',
										'Title'
								)
			);

			$People->group('V.Actvs_Id');

		// Usrs

			$Users = $this->db->select();

			$Users->from(
								array( 'V' => 'Vsts' ),
								array()
			);

			$Users->joinLeft(
								array( 'A' => 'Actvs' ),
								'V.Actvs_Id = A.Id',
								array(
										'Id'
								)
			);

			$Users->joinLeft(
								array( 'U' => 'Usrs' ),
								'V.Usrs_Id = U.Id',
								array(
										'FirstName' => new Zend_Db_Expr('CASE WHEN U.Privacy > 0 THEN U.First_Name ELSE NULL END'),
										'LastName' 	=> new Zend_Db_Expr('CASE WHEN U.Privacy > 2 THEN U.Last_Name ELSE NULL END'),
										'FB' 		=> new Zend_Db_Expr('CASE WHEN U.Privacy > 3 THEN U.Id ELSE NULL END'),
										'Link' 		=> new Zend_Db_Expr('CASE WHEN U.Privacy > 3 THEN U.Link ELSE NULL END')
								)
			);

			$Users->joinLeft(
								array( 'L' => 'Lctns' ),
								'U.Home = L.Id',
								array(
										'Country',
										'City' 		=> new Zend_Db_Expr('CASE WHEN U.Privacy > 1 THEN L.Locality ELSE NULL END')
								)
			);

			$Users->group( array( 'V.Actvs_Id', 'V.Usrs_Id') );
			$Users->order( array( 'FB DESC', 'Country', 'City', 'FirstName','LastName' ) );


		// Adding where

	    	$lat 	= round( $this->_getParam('lat'),7 );
	    	$lng 	= round( $this->_getParam('lng'),7 );
	    	$when 	= $this->_getParam('when');

	    	// Adding location constrains

			$Lctn = $this->db->fetchOne('SELECT Id FROM Lctns WHERE Lat = ? AND Lng = ?', array( $lat, $lng) );

			if ( $Lctn ){
				$TotalPeople->where('V.Lctns_Id = '.(int)$Lctn);
				$People->where('V.Lctns_Id = '.(int)$Lctn);
				$Users->where('V.Lctns_Id = '.(int)$Lctn);
			}

			// Adding Time constrains

	    	switch ( $when ) {
	    		case 'today':
					$TotalPeople->where('V.In >= CURDATE()');
					$People->where('V.In >= CURDATE()');
					$Users->where('V.In >= CURDATE()');
	    			break;

	    		case 'yesterday':
					$TotalPeople->where('V.In BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE()');
					$People->where('V.In BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE()');
					$Users->where('V.In BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE()');
	    			break;

	    		case 'realtime':
	    		default:
					$TotalPeople->where('V.In < NOW() AND V.Out > NOW()');
					$People->where('V.In < NOW() AND V.Out > NOW()');
					$Users->where('V.In < NOW() AND V.Out > NOW()');
	    			break;
			}

		// Making DB queries

//			die( $TotalPeople->__toString() );
			$this->view->TP = (int)$this->db->fetchOne( $TotalPeople->__toString() );

			$People_s 		= $this->db->query( $People );
//			die( $People->__toString() );
			$this->view->P 	= $People_s->fetchAll();

			$Users_s 		= $this->db->query( $Users );
//			die( $Users->__toString() );
			$this->view->U 	= $Users_s->fetchAll();
    }
}